package com.iris.live.services.common.reconsitution;

/**
 * sql 初始化
 * Created by yitianle
 * 2017/4/12.
 */
public class SqlConstant {


    //
    //public  static final String

    //SI查询
    public static  final  String GET_SI_NUM_LIST = "SELECT\n" +
            "	c.`车型编号`,\n" +
            "	a.`库存编号`,\n" +
            "	e.`金融手续费` `金融手续费应收`,\n" +
            "	f.`交强险手续费` + f.`商业险手续费` `保险手续费应收`,\n" +
            "	g.`精品成本` `后市场产品预计成本`,\n" +
            "	h.`厂家延保MSRP` `厂家延保预计成本`,\n" +
            "	h.`第三方延保MSRP` `第三方延保预计成本`,\n" +
            "	i.`开票客户姓名` `客户姓名`,\n" +
            "	i.`开票时间` `开票日期`,\n" +
            "	a.`生产号` `生产编号`,\n" +
            "	c.`级别` `车系`,\n" +
            "	c.`车型`,\n" +
            "	a.`实际MSRP含税` `建议零售价`,\n" +
            "	b.`销售顾问`,\n" +
            "	a.`车架号`,\n" +
            "	mm.`贴息金额`  贴息应收\n" +
            "	\n" +
            "FROM\n" +
            "	`LSH库存表` a\n" +
            "LEFT JOIN `报价单表` b ON (\n" +
            "	a.`报价编号` = b.`报价编号`\n" +
            "	AND b.`许可` =:permit\n" +
            ")\n" +
            "LEFT JOIN `LSH车型` c ON c.`车型编号` = a.`车型编号`\n" +
            "LEFT JOIN `报价金融表` AS e ON b.`金融` = e.`金融编号`\n" +
            "LEFT JOIN `报价保险表` AS f ON b.`保险编号` = f.`保险编号`\n" +
            "LEFT JOIN `报价精品表` AS g ON b.`精品` = g.`编号`\n" +
            "LEFT JOIN `报价五星延保` AS h ON b.`延保编号` = h.`延保编号`\n" +
            "LEFT JOIN `报价交车表` AS i ON b.`交车` = i.`编号`\n" +
            "LEFT JOIN lsh金融申请附属 AS mm on  b.`初始报价单号`=mm.初始报价单号\n" +
            "where	1 = 1\n";


    public static final String GET_SI_LIST = "SELECT\n" +
            "		a.type `调整类型`,\n" +
            "		a.adjust_date `调整日期`,\n" +
            "		b.`生产号` `生产编号`,\n" +
            "		IF ( bb.`来源` = 'PbP', CONCAT('PbP ', bb.`级别`), bb.`级别` ) `车系`,\n" +
            "		bb.`车型详细`,\n" +
            "		b.`车架号` `车辆VIN码`,\n" +
            "		b.`实际MSRP含税` `建议零售价`,\n" +
            "		ii.`销售顾问`,\n" +
            "		hh.`开票客户姓名` `客户姓名`,\n" +
            "		hh.`开票时间` `开票日期`,\n" +
            "		c.`随车成本` `SI应收`,\n" +
            //"		IF ( c.`随车成本` IS NULL, a.si_adjust_real, a.si_adjust_real + c.`随车成本` ) `SI实收`, dd.`金融手续费` `金融手续费应收`,\n" +
           // "		IF ( dd.`金融手续费` IS NULL, a.finance_poundage_adjust_real, a.finance_poundage_adjust_real + dd.`金融手续费` ) `金融手续费实收`,\n" +
            //"		ee.`交强险手续费` + ee.`商业险手续费` `保险手续费应收`,\n" +
            //"		IF ( ee.`交强险手续费` IS NULL, a.insurance_poundage_adjust_real, a.insurance_poundage_adjust_real + ee.`交强险手续费` + ee.`商业险手续费` ) `保险手续费实收`, ff.`精品成本` `后市场产品预估成本`,\n" +
            //"		IF ( ff.`精品成本` IS NULL, a.after_market_product_cost_adjust_real, a.after_market_product_cost_adjust_real + ff.`精品成本` ) `后市场产品实际成本`, gg.`延保原价` `五星延保预估成本`,\n" +
          //  "		IF ( gg.`延保原价` IS NULL, a.five_stars_extend_cost_adjust_real, a.five_stars_extend_cost_adjust_real + gg.`延保原价` ) `五星延保实际成本`, " +
           // "		yy.贴息金额 贴息金额应收,\n" +
           // "		IF ( yy.贴息金额 IS NULL, a.finance_soft_loan_adjust_real, a.finance_soft_loan_adjust_real + yy.贴息金额 ) `贴息金额实收`,\n" +
            "  a.si_adjust_real as `SI实收`,\n" +
            "  a.finance_poundage_adjust_befor as  `金融手续费应收`,\n" +
            "  a.finance_poundage_adjust_real as `金融手续费实收`,\n" +
            "  a.insurance_poundage_adjust_befor as `保险手续费应收`,\n" +
            "   IF ( 8 = :siType,  a.after_market_product_cost_buy_adjust_befor,  a.after_market_product_cost_adjust_befor ) as `精品产品预计成本`,\n" +
            "  a.insurance_poundage_adjust_real as `保险手续费实收`,\n " +
            "  a.five_stars_extend_cost_adjust_befor as `厂家延保预计成本`,\n" +
            "  IF ( 8 = :siType,  a.after_market_product_cost_buy_adjust_real,  a.after_market_product_cost_adjust_real ) as `精品产品实际成本`,\n " +
            "  a.five_stars_extend_cost_adjust_real as `厂家延保实际成本`,\n "  +
            "  a.third_extend_cost_adjust_befor as `第三方延保预计成本`,\n "  +
            "  a.third_extend_cost_adjust_real as `第三方延保实际成本`,\n "  +
            "  a.finance_soft_loan_adjust_befor as  贴息金额应收,\n" +
            "  a.finance_soft_loan_adjust_real as `贴息金额实收`,\n" +
            "		a.id\n" +
            "FROM\n" +
            "	income_adjustment a\n" +
            "LEFT JOIN `LSH库存表` AS b ON a.stock_code = b.`库存编号`\n" +
            "LEFT JOIN (\n" +
            "		SELECT\n" +
            "			b.`报价编号`,\n" +
            "			SUM(cc.`随车成本`) `随车成本`,\n" +
            "			b.`库存编号`\n" +
            "		FROM\n" +
            "			income_adjustment a\n" +
            "		LEFT JOIN (select max(b.报价编号) 报价编号 , a.库存编号 from `LSH库存表` a , 报价单表 b where a.库存编号 = b.库存编号 group by b.初始报价单号) AS b ON a.stock_code = b.`库存编号`\n" +
            "		LEFT JOIN `报价随车表` AS cc ON ( b.`报价编号` = cc.`报价编号` AND cc.`随车类型` = '厂家' )\n" +
            "		WHERE  a.permit = :permit GROUP BY b.`库存编号`\n" +
            ") AS c ON b.`库存编号` = c.`库存编号`\n" +
            "LEFT JOIN `报价单表` ii ON ( b.`报价编号` = ii.`报价编号` AND ii.`许可` =:permit)\n" +
            "LEFT JOIN `LSH车型` AS bb ON bb.`车型编号` = a.car_code\n" +
            "LEFT JOIN `报价金融表` AS dd ON ii.`金融` = dd.`金融编号`\n" +
            "LEFT JOIN `报价保险表` AS ee ON ii.`保险编号` = ee.`保险编号`\n" +
            "LEFT JOIN `报价精品表` AS ff ON ii.`精品` = ff.`编号`\n" +
            "LEFT JOIN `报价五星延保` AS gg ON ii.`延保编号` = gg.`延保编号`\n" +
            "LEFT JOIN `报价交车表` AS hh ON ii.`交车` = hh.`编号`\n" +
            "LEFT JOIN   `lsh金融申请附属`  AS  yy on yy. 初始报价单号=ii.初始报价单号\n" +
            "WHERE\n" +
            "	1 = 1 \n" +
            " AND a.permit =:permit";

    //SI bmbs查询
    public static final String GET_SI_BMBS_LIST = "SELECT\n" +
            "	income.`id`,\n" +
            "	income.`stock_code` as 库存编号,\n" +
            "	income.`adjust_date` as 调整日期,\n" +
            "	i.`开票客户姓名` `客户姓名`,\n" +
            "	i.`开票时间` `开票日期`,\n" +
            "	a.`生产号` `生产编号`,\n" +
            "	c.`级别` as `车系`,\n" +
            "	c.`车型` as 车型详细,\n" +
            "	a.`实际MSRP含税` `建议零售价`,\n" +
            "	b.`销售顾问`,\n" +
            "	a.`车架号` as 车辆VIN码,\n" +
            "	income.`type` as 调整类型,\n" +
            "	income.`p_num` as pNum,\n" +
            "	(select sum(BMBS_wholesale_SI) from snp_setting where carCode = income.car_code and y_m='190001' and snp_setting.permit=:permit)as 厂家SI_批售应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end)) and si_setting_id=24 and p_num =income.p_num ) as 厂家SI_批售实收,\n" +
            "	(select sum(BMBS_retail_SI) from snp_setting where carCode = income.car_code and y_m='190001' and snp_setting.permit=:permit)as 厂家SI_零售应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id in(1,2,3,4,5,7,8,11,12,13,14,15,17,20,21,22,23,25,26,29,30,31,33,34,35) and p_num =income.p_num) as 厂家SI_零售实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='保险' and 随车类型='厂家') as 厂家SI_保险应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id=10 and p_num =income.p_num) as 厂家SI_保险实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='购置税' and 随车类型='厂家')as 厂家SI_购置税应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end ) )  and si_setting_id=16 and p_num =income.p_num) as 厂家SI_购置税实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='SSSC' and 随车类型='厂家') as 厂家SI_SSSC应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id=27 and p_num =income.p_num) as 厂家SI_SSSC实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='旅游礼包' and 随车类型='厂家')as 厂家SI_旅游礼包应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end) )  and si_setting_id=18 and p_num =income.p_num) as 厂家SI_旅游礼包实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='SD卡' and 随车类型='厂家') as 厂家SI_SD卡应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id=32 and p_num =income.p_num) as 厂家SI_SD卡实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='加油卡' and 随车类型='厂家') as 厂家SI_加油卡应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id=9 and p_num =income.p_num) as 厂家SI_加油卡实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='实物礼品' and 随车类型='厂家')as 厂家SI_实物礼品应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id=28 and p_num =income.p_num) as 厂家SI_实物礼品实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='服务礼券' and 随车类型='厂家')as 厂家SI_服务礼券应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id=19 and p_num =income.p_num) as 厂家SI_服务礼券实收,\n" +
            "	(select sum(随车成本) from 报价随车表 where 报价编号 = a.报价编号 and 随车名称='SSSC' and 随车类型='厂家')as 调整应收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end) ) and si_setting_id=6 and p_num =income.p_num) as 调整实收 \n" +
            "	\n" +
            "FROM\n" +
            "	`income_adjustment` income\n" +
            "LEFT JOIN `LSH库存表` AS a on  income.`stock_code`=a.库存编号 "+
            "LEFT JOIN `报价单表` b ON a.`报价编号` = b.`报价编号`\n" +
            "LEFT JOIN `LSH车型` c ON income.`car_code` = c.`车型编号`\n" +
            "LEFT JOIN `报价交车表` AS i ON b.`交车` = i.`编号`\n" +
            "WHERE\n" +
            "income.permit=:permit\n" +
            " AND ( income.`si_adjust_real` IS NOT NULL OR income.`si_adjust_real` >0)";

    //SI bmbs初始化
    public static final String GET_SI_BMBS_INIT = "SELECT\n" +
            "	income.`id`,\n" +
            "	income.`adjust_date` `日期`,\n" +
            "	c.`品牌` as 品牌,\n" +
            "	c.`车型` as 车型,\n" +
            "	c.`级别` as `车系`,\n" +
            "	c.`车型详细` as 车款,\n" +
            "	income.`type` as 调整类型,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))   and si_setting_id=24 and p_num =income.p_num) as 厂家SI_批售实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))   and si_setting_id in(1,2,3,4,5,7,8,11,12,13,14,15,17,20,21,22,23,25,26,29,30,31,33,34,35) and p_num =income.p_num) as 厂家SI_零售实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id=10 and p_num =income.p_num) as 厂家SI_保险实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))   and si_setting_id=16 and p_num =income.p_num) as 厂家SI_购置税实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))   and si_setting_id=27 and p_num =income.p_num) as 厂家SI_SSSC实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))   and si_setting_id=18 and p_num =income.p_num) as 厂家SI_旅游礼包实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))  and si_setting_id=32 and p_num =income.p_num) as 厂家SI_SD卡实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))   and si_setting_id=9 and p_num =income.p_num) as 厂家SI_加油卡实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))   and si_setting_id=28 and p_num =income.p_num) as 厂家SI_实物礼品实收,\n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end))   and si_setting_id=19 and p_num =income.p_num) as 厂家SI_服务礼券实收, \n" +
            "	(select sum(si_amount) from si_bmbs_setting where ((case when income.type=1 then stock_id= income.stock_code else car_type_id = income.car_code end)) and si_setting_id=6 and p_num =income.p_num) as 贴息金额实收 \n" +
            "	\n" +
            "FROM\n" +
            "	`income_adjustment` income\n" +
            "LEFT JOIN `LSH车型` c ON income.`car_code` = c.`车型编号`\n" +
            "WHERE\n" +
            "income.permit=:permit\n"+
            "AND income.id =:id\n";

}
